﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccessLayer;

namespace BusinessLogicLayer
{
    class clsDatabaseUpdate
    {
        #region Properties
        private clsDataAccess DataAccess { get; set; }
        #endregion

        #region Constructors
        /// <summary>
        /// Does the update on the Database.
        /// </summary>
        /// <param name="pSettings">General settings.</param>
        public clsDatabaseUpdate(clsDataAccess pDataAccess)
        {
            this.DataAccess = pDataAccess;
        }
        #endregion

        #region Events
        #endregion

        #region Public methods
        /// <summary>
        /// Starts the update process.
        /// </summary>
        public Boolean Start()
        {
            Boolean Success = false;
            ArrayList QueryCollection = new ArrayList();
            String DateFields = "DateCreated DATETIME, DateChanged DATETIME, ";
            String UserFields = "IDUserCreated INT, IDUserChanged INT, ";
            String FirstFieldsWithoutID = DateFields + UserFields;
            String FirstFields = this.DataAccess.SQLTranslation.IDField + FirstFieldsWithoutID;

            if (DataAccess.DatabaseVersion() <= 0)
            {
                //Create the preferences table.
                QueryCollection.Add(new clsQuery("CREATE TABLE Preferences (" + this.DataAccess.SQLTranslation.IDField + DateFields + "DatabaseVersion INT NOT NULL);", "Creating the preferences table."));
                //TODO: Insert first preferences Table. Create method wich creates the dates of each table. Set this little part into an own method.
                QueryCollection.Add(new clsQuery("INSERT INTO Preferences (DateCreated, DateChanged, DatabaseVersion) VALUES ('" + DateTime.Now.ToString() + "', '" + DateTime.Now.ToString() + "', 1);", "Insert first preferences information."));

                //Create the user table.
                QueryCollection.Add(new clsQuery("CREATE TABLE Users (" + FirstFields + "FirstName VARCHAR(255), SecondName VARCHAR(255), Password VARCHAR(255));", "Creating the user table."));

                //Create the user rights table.
                QueryCollection.Add(new clsQuery("CREATE TABLE UsersRights (" + FirstFields + "IDUsers INT, FormName VARCHAR(255), RightToView BIT);", "Creating the user rights table."));

                //Execute all the SQL commands.
                DataAccess.ExecuteQueryCollection(QueryCollection);

                Success = Start();
            }
            else
            {
                if (this.DataAccess.Settings.DatabaseVersion_Database > this.DataAccess.Settings.DatabaseVersion_Program)
                {
                    //The database is used by a younger program release. The program must be updated.
                    Success = false;
                }
                else
                {
                    for (Int32 i = this.DataAccess.Settings.DatabaseVersion_Database; i < this.DataAccess.Settings.DatabaseVersion_Program; i++)
                    {
                        switch (i)
                        {
                            case 1:
                                //Create the stores table.
                                QueryCollection.Add(new clsQuery("CREATE TABLE Stores (" + FirstFields + "StoreName VARCHAR(255), Street VARCHAR(255), Postcode VARCHAR(255), City VARCHAR(255), Country VARCHAR(255));", "Create stores table."));
                                break;
                            case 2:
                                //Create a field for a store closed information.
                                QueryCollection.Add(new clsQuery("ALTER TABLE Stores ADD Closed INT;", "Add column to stores table."));
                                break;
                            case 3:
                                //Add translation possibility.
                                QueryCollection.Add(new clsQuery("CREATE TABLE Languages (" + FirstFields + "Language VARCHAR(255));", "Create languages table."));
                                QueryCollection.Add(new clsQuery("INSERT INTO Languages (DateCreated, DateChanged, IDUserCreated, IDUserChanged, Language) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'English');", "Insert language into languages table."));
                                QueryCollection.Add(new clsQuery("ALTER TABLE Users ADD IDLanguages INT;", "Add column to users table."));
                                QueryCollection.Add(new clsQuery("CREATE TABLE Translations (" + FirstFields + "FormName VARCHAR(255), ControlName VARCHAR(255), IDLanguages INT, Translation VARCHAR(255));", "Create translation table."));
                                break;
                            case 4:
                                //Add finance table.
                                QueryCollection.Add(new clsQuery("CREATE TABLE Finances (" + FirstFields + "DateConcerned DATETIME, Amount " + this.DataAccess.SQLTranslation.MoneyField + ", Description VARCHAR(255));", "Create finances table."));
                                break;
                            case 5:
                                //Add currency table.
                                QueryCollection.Add(new clsQuery("CREATE TABLE Currencies (" + FirstFields + "CurrencyName VARCHAR(255), CurrencyAbbrevation VARCHAR(255), ExchangeRateToOneEUR FLOAT, DateEffectiveFrom DATETIME);", "Create currencies table."));
                                QueryCollection.Add(new clsQuery("ALTER TABLE Finances ADD IDCurrencies INT;", "Add column to finances table."));
                                break;
                            case 6:
                                //TODO: Every currency has a specefic value. The EUR value is not 1.
                                QueryCollection.Add(new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'Euro', 'EUR', 1, " + this.DataAccess.SQLTranslation.GetDateString(DateTime.Parse("23.06.2009 00:00")) + ");", "Insert currency into currencies table."));
                                QueryCollection.Add(new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'U.S. Dollar', 'USD', 1.3845, " + this.DataAccess.SQLTranslation.GetDateString(DateTime.Parse("23.06.2009 00:00")) + ");", "Insert currency into currencies table."));
                                QueryCollection.Add(new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'Australian Dollar', 'AUD', 1.7587825, " + this.DataAccess.SQLTranslation.GetDateString(DateTime.Parse("23.06.2009 00:00")) + ");", "Insert currency into currencies table."));
                                QueryCollection.Add(new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'New Zealand Dollar', 'NZD', 2.19812966, " + this.DataAccess.SQLTranslation.GetDateString(DateTime.Parse("23.06.2009 00:00")) + ");", "Insert currency into currencies table."));
                                QueryCollection.Add(new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'Canadian Dollar', 'CAD', 1.59882026, " + this.DataAccess.SQLTranslation.GetDateString(DateTime.Parse("23.06.2009 00:00")) + ");", "Insert currency into currencies table."));
                                QueryCollection.Add(new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'Chinese Yuan', 'RMB', 9.47740335, " + this.DataAccess.SQLTranslation.GetDateString(DateTime.Parse("23.06.2009 00:00")) + ");", "Insert currency into currencies table."));
                                QueryCollection.Add(new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'Japanese Yen', 'JPY', 133.026379, " + this.DataAccess.SQLTranslation.GetDateString(DateTime.Parse("23.06.2009 00:00")) + ");", "Insert currency into currencies table."));
                                QueryCollection.Add(new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'Singapore Dollar', 'SGD', 2.01956992, " + this.DataAccess.SQLTranslation.GetDateString(DateTime.Parse("23.06.2009 00:00")) + ");", "Insert currency into currencies table."));
                                QueryCollection.Add(new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'Malaysian Ringgits', 'MYR', 4.90182194, " + this.DataAccess.SQLTranslation.GetDateString(DateTime.Parse("23.06.2009 00:00")) + ");", "Insert currency into currencies table."));
                                QueryCollection.Add(new clsQuery("INSERT INTO Currencies (DateCreated, DateChanged, IDUserCreated, IDUserChanged, CurrencyName, CurrencyAbbrevation, ExchangeRateToOneEUR, DateEffectiveFrom) VALUES (" + this.DataAccess.SQLTranslation.ActualDate + ", " + this.DataAccess.SQLTranslation.ActualDate + ", 0, 0, 'Indonesian Rupiahs', 'IDR', 14273.1959, " + this.DataAccess.SQLTranslation.GetDateString(DateTime.Parse("23.06.2009 00:00")) + ");", "Insert currency into currencies table."));
                                QueryCollection.Add(new clsQuery("ALTER TABLE Users ADD IDCurrencies INT;", "Add column to users table."));
                                break;
                        }
                        //Set the database version 1 up.
                        QueryCollection.Add(new clsQuery("UPDATE Preferences SET DatabaseVersion = DatabaseVersion + 1;", "Set database version one upwards."));
                    }
                    
                    DataAccess.ExecuteQueryCollection(QueryCollection);

                    Success = true;
                }
            }

            return Success;
        }
        #endregion

        #region Private methods
        #endregion
    }
}
